package cn.nwpu.eckel.Dao;

import static cn.nwpu.eckel.utility.DBConnection.close;
import static cn.nwpu.eckel.utility.DBConnection.getConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.nwpu.eckel.model.Forecast;
import cn.nwpu.eckel.model.MPS;
import cn.nwpu.eckel.model.Plan;

public class ForecastDao {
	public static List<Forecast> getForecastsByMpsNo(int mpsNo){
		List<Forecast> forecasts=new ArrayList<Forecast>();
		Forecast tempForecast;
		Connection con=getConnection();
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String sql="select * from forecast_tb where mpsNo=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,mpsNo);
			rs=pstmt.executeQuery();
			while(rs.next()){
				tempForecast=new Forecast(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getString(5),rs.getDate(6));
				forecasts.add(tempForecast);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return forecasts;
	}
	public static Forecast roll(int mpsNo){
		Forecast f=new Forecast();
		Connection con=getConnection();
		PreparedStatement pstmt_1=null;
		PreparedStatement pstmt_2=null;
		PreparedStatement pstmt_3=null;
		ResultSet rs=null;
		String sql_1="select * from forecast_tb where mpsNo=? and ts=0";
		String sql_2="delete from forecast_tb where mpsNo=? and ts=0";
		String sql_3="update forecast_tb set ts=ts-1 where mpsNo=?";
		try{
			pstmt_1=con.prepareStatement(sql_1);
			pstmt_2=con.prepareStatement(sql_2);
			pstmt_3=con.prepareStatement(sql_3);
			
			pstmt_1.setInt(1,mpsNo);
			rs=pstmt_1.executeQuery();
			
			pstmt_2.setInt(1,mpsNo);
			pstmt_2.executeUpdate();
			
			pstmt_3.setInt(1,mpsNo);
			pstmt_3.executeUpdate();
			
			while(rs.next()){
				f.id=rs.getInt(1);
				f.mpsNo=rs.getInt(2);
				f.ts=rs.getInt(3);
				f.quantity=rs.getInt(4);
				f.people=rs.getString(5);
				f.buildTime=rs.getDate(6);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt_1);
			close(pstmt_2);
			close(con);
		}
		return f;
	}

	public static Forecast deleteForecast(int id){
		Forecast forecast=ForecastDao.getForecastById(id);
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="delete from forecast_tb where id=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,id);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return forecast;
	}
	public static Forecast getForecastById(int id){
		Forecast forecast=new Forecast();
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="select* from forecast_tb where id=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,id);
			rs=pstmt.executeQuery();
			while(rs.next()){
				forecast.id=rs.getInt(1);
				forecast.mpsNo =rs.getInt(2);
				forecast.ts=rs.getInt(3);
				forecast.quantity=rs.getInt(4);
				forecast.people=rs.getString(5);
				forecast.buildTime=rs.getDate(6);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return forecast;
	}
	
    public static void insertForecast(Forecast forecast){
    	Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="insert into forecast_tb values(?,?,?,?,?,?)";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, forecast.id);
			pstmt.setInt(2, forecast.mpsNo);
			pstmt.setInt(3, forecast.ts);
			pstmt.setInt(4, forecast.quantity);
			pstmt.setString(5, forecast.people);
			pstmt.setDate(6, forecast.buildTime);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
	}
	public static Forecast alterForecast(Forecast newFc){
		Forecast oldFc=ForecastDao.getForecastById(newFc.id);
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="update forecast_tb set quantity=?,people=?,buildTime=? where mpsno=? and ts=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, newFc.quantity);
			pstmt.setString(2,newFc.people);
			pstmt.setDate(3,newFc.buildTime);
			pstmt.setInt(4,newFc.mpsNo);
			pstmt.setInt(5,newFc.ts);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return oldFc;
	}

}
